  
import java.text.DateFormat
import java.text.SimpleDateFormat
import groovy.sql.Sql
import java.util.Calendar
import java.util.GregorianCalendar
import org.apache.tools.ant.util.FileUtils


class WhereClauseService  implements java.io.Serializable{
    javax.sql.DataSource dataSource
	DateFormat dfMySql = new SimpleDateFormat("yyyy-MM-dd")
	StringBuffer whereClause 
	DataTypeService dataTypeService
	
	def String generateWhereClause(HashMap paramMap, Integer systemId) {
		def sql = getSql()
		whereClause = new StringBuffer()
		
		def rptSystem = ReportSystem.get(systemId)
		def dbType = rptSystem.databaseType
		
		//println "dbType = "+dbType
		paramMap.each{key,param->
			//println "param in generateWhereClause "+param
			String dataType = param.dataType.toString()
				if (dataType == DataTypeService.dateDataType){
					if(rptSystem.databaseType.toUpperCase()=="ORACLE")
						applyDateFilterOracle(param)
				    else{
						applyDateFilterMySql(param)
					//println "after applyDateFilterMySql "+whereClause
					}
				} 
				else if (dataType== DataTypeService.numDataType) {
					applyNumberFilter(param)
				} 
				else if (dataType == DataTypeService.strDataType) {
					applyStringFilter(param)
				} 
				else if (dataType == DataTypeService.codeDataType | 
				         dataType == DataTypeService.entityDataType) {
					applyCodeEntityFilter(param)
				} 
				else if (dataType == DataTypeService.booleanDataType) {
					applyBooleanFilter(param)
				} 
				else if(dataType == DataTypeService.yesNoDataType){
					applyYesNoFilter(param)			
				}			
		}
		
		String tmpWhereClause =""
		if(whereClause)
			tmpWhereClause = "WHERE "+whereClause.toString()
		println "whereClause = " + tmpWhereClause
        return tmpWhereClause

    }
	
	private HashMap assignParameter(nonFilterableParam){
	    HashMap parameters = [:]
		nonFilterableParam.each{param->			
		    if(param.dataType == Integer.valueOf(dataTypeService.rtfDataType)){
				String rtfText
				//rtfText = FileUtils.readFully(new FileReader(new File(param.parameterValue,param.parameterValue2)))
				File file = new File(param.parameterValue)
				if(file.canRead())
					rtfText = FileUtils.readFully(new FileReader(file))
					
				parameters.put(param.parameterName,rtfText)
			}
			else
				parameters.put(param.parameterName,param.parameterValue)
 		}
		return parameters
	}
	
	private applyDateFilterMySql(param){
		String id = param.idOperatorSelected.toString()
		if (id == OperatorService.allDates) {// all dates
			// include All dates so do nothing			
		} 
		else if (id == OperatorService.lastWeek) {// Last week	
			Calendar cal = getLastWeekDate()		
			
			Calendar cal2 = new GregorianCalendar()
			cal2.setTime(cal.getTime())
			cal2.add(Calendar.DAY_OF_MONTH, 6)
			
			String dateLastWeek = dateToMySqlDateString(cal,cal2)
			
			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName +",'%Y-%m-%d')" +
						" BETWEEN " +dateLastWeek)				
			} else {
				whereClause.append(" DATE_FORMAT(" + param.parameterName +",'%Y-%m-%d')" +
						" BETWEEN " +dateLastWeek)
			}			
		} 
		else if (id == OperatorService.lastMonth) {// Last Month
			Calendar cal = getLastMonthDate()
			Calendar cal2 = new GregorianCalendar()
			cal2.setTime(cal.getTime())
			cal2.add(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH)-1)
			String lastMonthDate = dateToMySqlDateString(cal,cal2)
			
			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName +",'%Y-%m-%d') " +
						" BETWEEN " + lastMonthDate)
			} else {
				whereClause.append(" DATE_FORMAT("+param.parameterName +",'%Y-%m-%d') " +
						" BETWEEN " + lastMonthDate)
			}
		} else if (id == OperatorService.dateBetween && param.dateBetween1 && param.dateBetween2) {
			Calendar cal = new GregorianCalendar()
			Calendar cal2 = new GregorianCalendar()
			cal.setTime(param.dateBetween1)
			cal2.setTime(param.dateBetween2)
			
			String dateBetween = dateToMySqlDateString(cal,cal2)
			//println "dateBetween = " + dateBetween
			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName + ",'%Y-%m-%d') "
						+ " BETWEEN " + dateBetween)
			} else {
				whereClause.append(" DATE_FORMAT("+param.parameterName + ",'%Y-%m-%d') " 
						+ " BETWEEN "+dateBetween)
			}
		} else if (id == OperatorService.datePastDays && param.datePastDay) {// dates within the past days
			java.util.Date date = new java.util.Date()
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(date)
			calendar1.add(Calendar.DAY_OF_MONTH, -param.datePastDay)
			String days = dateToMySqlDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName + 
						",'%Y-%m-%d') >= "	+ days)

			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName + 
						",'%Y-%m-%d') >= " + days)
			}
		} else if (id == OperatorService.datePastMonths && param.datePastMonth) {// dates within the past months
			java.util.Date date = new java.util.Date()
			Calendar cal = new GregorianCalendar()
			cal.setTime(date)
			cal.add(Calendar.MONTH, -param.datePastMonth)
			cal.add(Calendar.DAY_OF_MONTH, - (cal.get(Calendar.DAY_OF_MONTH)-1))
			
			String months = dateToMySqlDateString(cal)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName + 
						",'%Y-%m-%d') >= "	+ months)
			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName + 
						",'%Y-%m-%d') >= " + months)
			}
		} else if (id == OperatorService.dateLessEqual && param.dateLessEqual) {// Less than or Equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateLessEqual)
			String dateAsOf = dateToMySqlDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName + 
						",'%Y-%m-%d') <= "	+ dateAsOf)
			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName + 
						",'%Y-%m-%d') <= " + dateAsOf)
			}
		} else if (id == OperatorService.dateGreaterEqual && param.dateGreaterEqual) {// >= greater then or equal to
			//println"in greaterEqual mysql "
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateGreaterEqual)
			String dateGrEq = dateToMySqlDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName  + 
						",'%Y-%m-%d') >= "	+ dateGrEq)
			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName  + 
						",'%Y-%m-%d') >= " + dateGrEq)
			}
		} else if (id == OperatorService.dateEqual && param.dateEqual) {// = equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateEqual)
			String dateEq = dateToMySqlDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName  + ",'%Y-%m-%d') = "
						+ dateEq)
			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName  + ",'%Y-%m-%d') = " + dateEq)
			}
		}
	}
	
	private applyDateFilterOracle(param){
		//println "in applyDateFilterOracle"
		String id = param.idOperatorSelected.toString()
		if (id == OperatorService.allDates) {// all dates
			// include All dates so do nothing			
		} 
		else if (id == OperatorService.lastWeek) {// Last week	
			Calendar cal = getLastWeekDate()		
			
			Calendar cal2 = new GregorianCalendar()
			cal2.setTime(cal.getTime())
			cal2.add(Calendar.DAY_OF_MONTH, 6)
			
			String dateLastWeek = dateToOracleDateString(cal,cal2)
			
			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName +")" +
						" BETWEEN " +dateLastWeek)				
			} else {
				whereClause.append(" TRUNC(" + param.parameterName +")" +
						" BETWEEN " +dateLastWeek)
			}			
		} 
		else if (id == OperatorService.lastMonth) {// Last Month
			Calendar cal = getLastMonthDate()
			Calendar cal2 = new GregorianCalendar()
			cal2.setTime(cal.getTime())
			cal2.add(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH)-1)
			String lastMonthDate = dateToOracleDateString(cal,cal2)
			
			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName +") " +
						" BETWEEN " + lastMonthDate)
			} else {
				whereClause.append(" TRUNC("+param.parameterName +") " +
						" BETWEEN " + lastMonthDate)
			}
		} else if (id == OperatorService.dateBetween && param.dateBetween1 && param.dateBetween2) {//date between
			Calendar cal = new GregorianCalendar()
			Calendar cal2 = new GregorianCalendar()
			cal.setTime(param.dateBetween1)
			cal2.setTime(param.dateBetween2)
			
			String dateBetween = dateToOracleDateString(cal,cal2)
			//println "dateBetween = " + dateBetween
			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName + ") "
						+ " BETWEEN " + dateBetween)
			} else {
				whereClause.append(" TRUNC("+param.parameterName + ") " 
						+ " BETWEEN "+dateBetween)
			}
		} else if (id == OperatorService.datePastDays && param.datePastDay) {// dates within the past days
			java.util.Date date = new java.util.Date()
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(date)
			calendar1.add(Calendar.DAY_OF_MONTH, -param.datePastDay)
			String days = dateToOracleDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName + 
						") >= "	+ days)

			} else {
				whereClause.append(" TRUNC("+ param.parameterName + 
						") >= " + days)
			}
		} else if (id == OperatorService.datePastMonths && param.datePastMonth) {
			java.util.Date date = new java.util.Date()
			Calendar cal = new GregorianCalendar()
			cal.setTime(date)
			cal.add(Calendar.MONTH, -param.datePastMonth)
			cal.add(Calendar.DAY_OF_MONTH, - (cal.get(Calendar.DAY_OF_MONTH)-1))
			
			String months = dateToOracleDateString(cal)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName + 
						") >= "	+ months)
			} else {
				whereClause.append(" TRUNC("+ param.parameterName + 
						") >= " + months)
			}
		} else if (id == OperatorService.dateLessEqual && param.dateLessEqual) {// Less than or Equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateLessEqual)
			String dateAsOf = dateToOracleDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName + 
						") <= "	+ dateAsOf)
			} else {
				whereClause.append(" TRUNC("+ param.parameterName + 
						") <= " + dateAsOf)
			}
		} else if (id == OperatorService.dateGreaterEqual && param.dateGreaterEqual) {// >= greater then or equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateGreaterEqual)
			String dateGrEq = dateToOracleDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName  + 
						") >= "	+ dateGrEq)
			} else {
				whereClause.append(" TRUNC("+ param.parameterName  + 
						") >= " + dateGrEq)
			}
		} else if (id == OperatorService.dateEqual && param.dateEqual) {// = equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateEqual)
			String dateEq = dateToOracleDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName  + ") = "
						+ dateEq)
			} else {
				whereClause.append(" TRUNC("+ param.parameterName  + ") = " + dateEq)
			}
		}
	}
	
	private applyNumberFilter(param){
		String id = param.idOperatorSelected.toString()
		if (id == OperatorService.numEqual && param.numEqual) { // equals
			if (whereClause.size() > 0)
				whereClause.append(" and " + param.parameterName + " = " + param.numEqual)
			else 
				whereClause.append(param.parameterName + " = "	+ param.numEqual)
		} 
		else if (id == OperatorService.numNotEqual && param.numNotEqual) { // not equal to
			if (whereClause.length() > 0) 
				whereClause.append(" and " + param.parameterName+ " <> " + param.numNotEqual)
			else 
				whereClause.append(param.parameterName + " <> "+ param.numNotEqual)
		} 
		else if (id == OperatorService.numBetween && param.numBetween1 && param.numBetween2) { // between
			if (whereClause.length() > 0) 
				whereClause.append(" and " + param.parameterName
						+ " BETWEEN " + param.numBetween1 + " and " + param.numBetween2)
			else 
				whereClause.append(param.parameterName + " BETWEEN "
							+ param.numBetween1 + " and " + param.numBetween2)
		} 
		else if (id == OperatorService.numLessEqual && param.numLessEqual) {// Less then or = to
			if (whereClause.length() > 0) 
				whereClause.append(" and " + param.parameterName+ " <= " + param.numLessEqual)
			else
				whereClause.append(param.parameterName + " <= "+ param.numLessEqual)
		} 
		else if (id == OperatorService.numGreaterEqual && param.numGreaterEqual) {// Greater then or = to
			if (whereClause.length() > 0) 
				whereClause.append(" and " + param.parameterName+ " >= " + param.numGreaterEqual)
			else 
				whereClause.append(param.parameterName + " >= "+ param.numGreaterEqual)
		}
		
	}
	
	private applyStringFilter(param){
		String id = param.idOperatorSelected.toString()
		if (param.strInput){
			if (id == OperatorService.strStartsWith) { // Starts With
				if (whereClause.size() > 0) {
					whereClause.append(" and UPPER("
							+ param.parameterName.trim() + ") LIKE "
							+ "'" + param.strInput.toUpperCase().trim() + "%' ")
				} else {
					whereClause.append(" UPPER("
							+ param.parameterName + ") LIKE "
							+ "'" + param.strInput.toUpperCase() + "%' ")
				}
			} 
			else if (id == OperatorService.strContains) { // Contains
				if (whereClause.size() > 0) {
					whereClause.append(" and UPPER(" + param.parameterName + ")" + 
							" LIKE '%" + param.strInput.toUpperCase() + "%' ")
				} else {
					whereClause.append(" UPPER(" + param.parameterName + ")" +
							" LIKE '%" + param.strInput.toUpperCase() + "%' ")
				}
			} 
			else if (id == OperatorService.strMatchesExactly) { // Matches exactly not case sensitive
				if (whereClause.size() > 0) {
					whereClause.append(" and UPPER("
							+ param.parameterName + ") = " + "'" + param.strInput.toUpperCase()
							+ "' ")
				} else {
					whereClause.append(" UPPER("+ param.parameterName + ") = "
							+ "'" + param.strInput.toUpperCase() + "' ")
				}
			}
		}
	}
	
	private applyCodeEntityFilter(param){
		String id = param.idOperatorSelected.toString()
		StringBuffer strCode = new StringBuffer()
		String tmpClause =""
		
		if (param.codeSelectedList) {
		//Review this code to see if you can use the each key word for looping through the list.
		// if the PK is a number for CODE and Entity should always have a number for it's Primary Key
			if (id == OperatorService.codeIdIsNumber | 
			    id == OperatorService.none) { 
				for(int i = param.codeSelectedList.size()-1;i>=0;i--){
				    Code code = param.codeSelectedList[i]
					if(strCode)
						strCode.append(", "+code.id)
					else
						strCode.append("("+code.id)
				}
			} 
			else { // string
				for(int i = param.codeSelectedList.size()-1;i>=0;i--){
				    Code code = param.codeSelectedList[i]
					if(strCode)
						strCode.append(", '"+code.id +"'")
					else
						strCode.append("('"+code.id + "'")
				}
			}

			if (strCode.size() > 0) {
				// "(" is added above before the first value and ")" is added
				// after the last value for the 'IN' operator for oracle
				strCode.append(")")
				tmpClause = param.parameterName + " IN " + strCode
				if (whereClause.size() > 0)
					whereClause.append(" and " + tmpClause)
				else
					whereClause.append(tmpClause)				
			}
		}
	}
	
	private applyYesNoFilter(param){
		String id = param.idOperatorSelected.toString()
		if(id ==OperatorService.yesNoY){//Yes 
			if (whereClause.size() > 0) {
				whereClause.append(" and " +  "Upper(" + param.parameterName + 
						") LIKE 'Y%' ")
			} else {
				whereClause.append("Upper(" + param.parameterName + ") LIKE 'Y%' ")
			}
		}
		else if(id == OperatorService.yesNoN){//No
			if (whereClause.size() > 0) {
				whereClause.append(" and " +  "Upper(" + param.parameterName + 
						") LIKE 'N%' ")
			} else {
				whereClause.append("Upper(" + param.parameterName + ") LIKE 'N%' ")
			}
		}
	}
	
	private applyBooleanFilter(param){
		String id = param.idOperatorSelected.toString()
		if(id ==OperatorService.booleanTrue){//True
			if (whereClause.size() > 0) {
				whereClause.append(" and " +  param.parameterName + " = 1")
			} else {
				whereClause.append(param.parameterName + " = 1 ")
			}
		}
		else if(id == OperatorService.booleanFalse){// False
			if (whereClause.size() > 0) {
				whereClause.append(" and " +  param.parameterName +	" = 0 ")
			} else {
				whereClause.append(param.parameterName + " = 0 ")
			}
		}
	}
	
	public Calendar getLastWeekDate(){
		Calendar cal = new GregorianCalendar()
		cal.setTime(new Date())
		if(cal.get(Calendar.DAY_OF_WEEK)==1)
			cal.add(Calendar.DAY_OF_MONTH, -7)
		else if(cal.get(Calendar.DAY_OF_WEEK)==2)
			cal.add(Calendar.DAY_OF_MONTH, -8)
		else if(cal.get(Calendar.DAY_OF_WEEK)==3)
			cal.add(Calendar.DAY_OF_MONTH, -9)
		else if(cal.get(Calendar.DAY_OF_WEEK)==4)
			cal.add(Calendar.DAY_OF_MONTH, -10)
		else if(cal.get(Calendar.DAY_OF_WEEK)==5)
			cal.add(Calendar.DAY_OF_MONTH, -11)
		else if(cal.get(Calendar.DAY_OF_WEEK)==6)
			cal.add(Calendar.DAY_OF_MONTH, -12)
		else if(cal.get(Calendar.DAY_OF_WEEK)==7)
			cal.add(Calendar.DAY_OF_MONTH, -13)
		return cal
	}
	
	public Calendar getLastMonthDate(){
		Calendar cal = new GregorianCalendar()
		cal.setTime(new Date())
		cal.add(Calendar.MONTH, -1)//take us back 1 month
		cal.add(Calendar.DAY_OF_MONTH, - (cal.get(Calendar.DAY_OF_MONTH)-1))//take us back to the first of the month
		return cal
	}
	
	//return the date as an MySQL date string
	public String dateToMySqlDateString(Calendar cal){
		//MySQL date string example: '2008-01-22'
		String date="'"+dfMySql.format(cal.getTime())+"'"
		return date
	}
	
	//return the date as an MySQL date string
	public String dateToMySqlDateString(Calendar cal, Calendar cal2){
		//MySQL date string example: '2008-01-22'
		String date="'"+dfMySql.format(cal.getTime()) + "' and '" + dfMySql.format(cal2.getTime())+"'"
		return date
	}
	
	//return the date as an Oralce date string
	public String dateToOracleDateString(Calendar cal){
		//Oracle date string example: '1-jan-2008'
		String date=""
		String year1 = Integer.toString(cal.get(Calendar.YEAR))
		// calendar1.MONTH start from 0-11 so we have to add 1.
		String month1 = Integer.toString((cal.get(Calendar.MONTH) + 1))
		String day1 = Integer.toString(cal.get(Calendar.DAY_OF_MONTH))
		date="'"+day1+"-"+getMonthShort(month1)+"-"+ year1+ "' "
		return date
	}
	
	//return the two date as an Oracle date string with 
	//the word 'and' between the dates
	public String dateToOracleDateString(Calendar cal,Calendar cal2){
		String date=""
		
		String year1 = Integer.toString(cal.get(Calendar.YEAR))
		// calendar1.MONTH start from 0-11 so we have to add 1.
		String month1 = Integer.toString((cal.get(Calendar.MONTH) + 1))
		String day1 = Integer.toString(cal.get(Calendar.DAY_OF_MONTH))
		String year2 = Integer.toString(cal2.get(Calendar.YEAR))
		String month2 = Integer.toString((cal2.get(Calendar.MONTH) + 1))
		String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH))
		
		date="'"+day1+"-"+getMonthShort(month1)+"-"+ year1+ 
		     "' and " + 
		     "'"+day2+"-"+getMonthShort(month2)+"-"+ year2+"' "
		return date
	}
	
	private getMonthShort(String month){
		if(month=='1')
			return 'jan'
		else if(month =='2')
			return 'feb'
		else if(month =='3')
			return 'mar'
		else if(month =='4')
			return 'apr'
		else if(month =='5')
			return 'may'
		else if(month =='6')
			return 'jun'
		else if(month =='7')
			return 'jul'
		else if(month =='8')
			return 'aug'
		else if(month =='9')
			return 'sep'
		else if(month =='10')
			return 'oct'
		else if(month =='11')
			return 'nov'
		else if(month =='12')
			return 'dec'
	}

    private getSql(){
        return new Sql(dataSource)
    }

 

}